from sqlalchemy import Column, String, create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class Province(Base):
    __tablename__ = 'province'

    # create table province(name varchar(50) primary key,number varchar(20));
    name = Column(String(50), primary_key=True)
    number = Column(String(20))


def insertProvinces(lists):
    # 初始化数据库连接: 格式:'数据库类型+数据库驱动名称://用户名:口令@机器地址:端口号/数据库名'
    engine = create_engine('mysql+mysqlconnector://root:root@localhost:3306/appserver')
    # 创建DBSession类型:
    DBSession = sessionmaker(bind=engine)

    # 创建session对象:
    session = DBSession()

    for item in lists:
        print('name:%s number:%s' % (item.name, item.number))
        # 创建新Province对象:
        new_province = Province(name=item.name, number=item.number)
        # 添加到session:
        session.add(new_province)

    # 提交即保存到数据库:
    session.commit()
    # 关闭session:
    session.close()
